﻿Module ProjectMemberRoleDao

    Public Function searchDataByFilter(ByRef projectMemberRole As ProjectMemberRole) As Data.DataTable
        DataAccessConnection.openConnection()

        Dim command As New OracleClient.OracleCommand
        Dim table As New Data.DataTable

        Dim projectMember As New ProjectMember
        Dim role As New Role

        Dim query As String = ""

        query = " SELECT pmr.id                   pmr_id, "
        query &= "       pmr.role_id              pmr_role_id, "
        query &= "       r.name                   r_name, "
        query &= "       pmr.project_member_id    pmr_project_member_id"
        query &= "       FROM " & projectMemberRole.getTableName & " pmr, " & role.getTableName & " r "
        query &= "       WHERE (pmr.project_member_id = :project_member_id OR 0 = :project_member_id)"
        query &= "       AND (pmr.role_id = :role_id OR 0 = :role_id)"
        query &= "       AND (pmr.id = :id OR 0 = :id)"
        query &= "       AND (r.id = pmr.role_id)"

        command.CommandType = CommandType.Text
        command.CommandText = query
        command.Connection = connection
        command.Parameters.AddWithValue("project_member_id", If(projectMemberRole.projectMemberProperty Is Nothing, Nothing, projectMemberRole.projectMemberProperty.idProperty))
        command.Parameters.AddWithValue("role_id", If(projectMemberRole.roleProperty Is Nothing, Nothing, projectMemberRole.roleProperty.idProperty))
        command.Parameters.AddWithValue("id", projectMemberRole.idProperty)

        table.Load(command.ExecuteReader)

        DataAccessConnection.closeConnection()

        Return table
    End Function

    Public Function searchData(ByRef projectMemberRole As ProjectMemberRole) As ArrayList
        Dim table As New Data.DataTable
        Dim list As New ArrayList
        table = searchDataByFilter(projectMemberRole)
        list = convertTableToList(table)
        Return list
    End Function

    Private Function convertTableToList(ByRef table As Data.DataTable) As ArrayList
        Dim listProjectMemberRole As New ArrayList

        For Each row In table.Rows
            Dim projectMemberRole As New ProjectMemberRole
            projectMemberRole.idProperty = row("pmr_id")

            Dim projectMember As New ProjectMember
            projectMember.idProperty = row("pmr_project_member_id")
            projectMemberRole.projectMemberProperty = projectMember

            Dim role As New Role
            role.idProperty = row("pmr_role_id")
            role.nameProperty = row("r_name")
            projectMemberRole.roleProperty = role

            listProjectMemberRole.Add(projectMemberRole)
        Next


        Return listProjectMemberRole
    End Function
End Module
